#! /bin/bash

/export/server/presto/bin/presto --server hadoop01:8090 --catalog hive --schema default --execute \
" \
\
insert into mysql.zxjy.day_school \
select "day", \
       itcast_school_id, \
       itcast_school_name, \
       cast(pay_count as int) \
from hive.dws.day_sign \
where group_type = 'school'; \
 \
insert into mysql.zxjy.month_school \
select "month", \
       itcast_school_id, \
       itcast_school_name, \
       cast(pay_count as int) \
from hive.dm.month_sign \
where group_type = 'school'; \
 \
insert into mysql.zxjy.year_school \
select "year", \
       itcast_school_id, \
       itcast_school_name, \
       cast(pay_count as int) \
from hive.dm.year_sign \
where group_type = 'school'; \
 \
\
insert into mysql.zxjy.day_origin_school \
select "day", \
       origin_type, \
       itcast_school_id, \
       itcast_school_name, \
       cast(pay_count as int) \
from hive.dws.day_sign \
where group_type = 'origin_school'; \
 \
insert into mysql.zxjy.month_origin_school \
select "month", \
       origin_type, \
       itcast_school_id, \
       itcast_school_name, \
       cast(pay_count as int) \
from hive.dm.month_sign \
where group_type = 'origin_school'; \
 \
insert into mysql.zxjy.year_origin_school \
select "year", \
       origin_type, \
       itcast_school_id, \
       itcast_school_name, \
       cast(pay_count as int) \
from hive.dm.year_sign \
where group_type = 'origin_school'; \
 \
\
insert into mysql.zxjy.day_origin_subject \
select "day", \
       origin_type, \
       itcast_subject_id, \
       itcast_subject_name, \
       cast(pay_count as int) \
from hive.dws.day_sign \
where group_type = 'origin_subject'; \
 \
insert into mysql.zxjy.month_origin_subject \
select "month", \
       origin_type, \
       itcast_subject_id, \
       itcast_subject_name, \
       cast(pay_count as int) \
from hive.dm.month_sign \
where group_type = 'origin_subject'; \
 \
insert into mysql.zxjy.year_origin_subject \
select "year", \
       origin_type, \
       itcast_subject_id, \
       itcast_subject_name, \
       cast(pay_count as int) \
from hive.dm.year_sign \
where group_type = 'origin_subject'; \
 \
\
insert into mysql.zxjy.day_origin_school_subject \
select "day", \
       origin_type, \
       itcast_school_id, \
       itcast_school_name, \
       itcast_subject_id, \
       itcast_subject_name, \
       cast(pay_count as int) \
from hive.dws.day_sign \
where group_type = 'origin_school_subject'; \
 \
insert into mysql.zxjy.month_origin_school_subject \
select "month", \
       origin_type, \
       itcast_school_id, \
       itcast_school_name, \
       itcast_subject_id, \
       itcast_subject_name, \
       cast(pay_count as int) \
from hive.dm.month_sign \
where group_type = 'origin_school_subject'; \
 \
insert into mysql.zxjy.year_origin_school_subject \
select "year", \
       origin_type, \
       itcast_school_id, \
       itcast_school_name, \
       itcast_subject_id, \
       itcast_subject_name, \
       cast(pay_count as int) \
from hive.dm.year_sign \
where group_type = 'origin_school_subject'; \
 \
\
insert into mysql.zxjy.day_origin_type_channel \
select "day", \
       origin_type, \
       origin_channel, \
       cast(pay_count as int) \
from hive.dws.day_sign \
where group_type = 'origin_type_channel'; \
 \
insert into mysql.zxjy.month_origin_type_channel \
select "month", \
       origin_type, \
       origin_channel, \
       cast(pay_count as int) \
from hive.dm.month_sign \
where group_type = 'origin_type_channel'; \
 \
insert into mysql.zxjy.year_origin_type_channel \
select "year", \
       origin_type, \
       origin_channel, \
       cast(pay_count as int) \
from hive.dm.year_sign \
where group_type = 'origin_type_channel'; \
 \
\
insert into mysql.zxjy.day_origin_department \
select "day", \
       origin_type, \
       department_name, \
       cast(pay_count as int) \
from hive.dws.day_sign \
where group_type = 'origin_department'; \
 \
insert into mysql.zxjy.month_origin_department \
select "month", \
       origin_type, \
       department_name, \
       cast(pay_count as int) \
from hive.dm.month_sign \
where group_type = 'origin_department'; \
 \
insert into mysql.zxjy.year_origin_department \
select "year", \
       origin_type, \
       department_name, \
       cast(pay_count as int) \
from hive.dm.year_sign \
where group_type = 'origin_department'; \
 \
\
insert into mysql.zxjy.day_origin_intent \
select "day", \
       origin_type, \
       cast(pay_count as int), \
       cast(relationship_count as int), \
       round(pay_count / cast(relationship_count as double) * 100, 2) \
from hive.dws.day_sign \
where group_type = 'origin'; \
 \
insert into mysql.zxjy.month_origin_intent \
select "month", \
       origin_type, \
       cast(pay_count as int), \
       cast(relationship_count as int), \
       round(pay_count / cast(relationship_count as double) * 100, 2) \
from hive.dm.month_sign \
where group_type = 'origin'; \
 \
insert into mysql.zxjy.year_origin_intent \
select "year", \
       origin_type, \
       cast(pay_count as int), \
       cast(relationship_count as int), \
       round(pay_count / cast(relationship_count as double) * 100, 2) \
from hive.dm.year_sign \
where group_type = 'origin'; \
 \
\
insert into mysql.zxjy.day_origin_valid \
select "day", \
       origin_type, \
       cast(pay_count as int), \
       cast(valid_count as int), \
       round(pay_count / cast(valid_count as double) * 100, 2) \
from hive.dws.day_sign \
where group_type = 'origin'; \
 \
insert into mysql.zxjy.month_origin_valid \
select "month", \
       origin_type, \
       cast(pay_count as int), \
       cast(valid_count as int), \
       round(pay_count / cast(valid_count as double) * 100, 2) \
from hive.dm.month_sign \
where group_type = 'origin'; \
 \
insert into mysql.zxjy.year_origin_valid \
select "year", \
       origin_type, \
       cast(pay_count as int), \
       cast(valid_count as int), \
       round(pay_count / cast(valid_count as double) * 100, 2) \
from hive.dm.year_sign \
where group_type = 'origin'; \
"